Storage Locations – Using Map-Set from Database

Map-Sets do not have to be memory only. They can have a map-set structure stored in a database table. This can be queried in using some SQL within the storage location.

SYNTAX

The following code shows the syntax required to create a map-set from a table in the database:

<fm:storage-location name="name">
  <fm:cache-key string="string"/>
  <fm:new-document>
     <fm:root-element>map-set-list</fm:root-element>
  </fm:new-document>
  <fm:database>
    <fm:query>
      <fm:sql>
SQL STATEMENT
      </fm:sql>
    </fm:query>
  </fm:database>
</fm:storage-location>

Note that the SQL statement is contained within the fm:database part of the Storage Location.

CONCEPTS

Some Lists of Values (LOV) are quite lengthy and may be reused in a number of modules. In this case, the map-set XML data is preloaded in a table. This is often a manual task.

To access this data a Storage Location is defined within database access clauses (See Example).

A map-set can then be defined using this Storage Location.

Things to note:

• No constructor (fm:do block) is required within the map-set list as data is already created in the table
• Special value of 999999999 on refresh-timeout-mins used as you never need to refresh
• It is possible to use a constructor block that augments (adds to) XML in the table. This will be covered later

EXAMPLE

The following code will create a Storage Location that contains a map-set:

<fm:storage-location name="chems-sl">
  <fm:cache-key string="chems lov"/>
  <fm:new-document>
    <fm:root-element>map-set-list</fm:root-element>
  </fm:new-document>
  <fm:database>
  <fm:query>
    <fm:sql>
SELECT data
FROM env_mapsets
WHERE domain = 'CHEMICAL_LABEL_CODES'
FOR UPDATE OF data
    </fm:sql>
  </fm:query>
  </fm:database>
</fm:storage-location>

NB: FOR UPDATE OF locks the row to stop the data from changing.

The map-set declaration will look similar to the following code:

<fm:map-set name="chems">
  <fm:storage-location>chems-sl</fm:storage-location>
  <fm:do/>
  <fm:refresh-timeout-mins>999999999</fm:refresh-timeout-mins>
  <fm:refresh-in-background>false</fm:refresh-in-background>
</fm:map-set>

The fm:do block is empty because all the work has been done in the Storage Location.

NB: if you want your map-set to update instantly, you must use the <fm:do> <fm:run-query/> method, or call <fm:refresh-map-set/>.

EXERCISES

Please use your XX_EMPLOYEEMODULE (where XX are your initials) file for the following exercises.

Exercise 1

Add a new element to /theme/EMPLOYEE_LIST/EMPLOYEE before Forename called “TITLE”. This should be linked to a mapset called “ms-title”. The map-set needs to select its data from envmgr.env_mapsets where the DOMAIN is “PERSON_TITLE”.